//This class is responsible for performing all the database operations
//required to create, find and manipulate Client objects.

package project;
import java.sql.*;
import javax.swing.JTable;
import java.security.*;

public class ClientManager
{
	private String input = null, type = null;
	Client client;
	Connection conn;
	private Table table = null;
	private JTable jTable = null;
	private String stringPass = null;
	
	static String UPDATE_CUST = "UPDATE customerTable SET custId=?, "+
					"custPassword=?,idNumber=?,surname=?,name=?,"+
					"initials=?, title=?, address1=?,"+
					"address2=?, address3=?, address4=?, phone1=?,"+
					"phone2=?, email=?, birthDate=?, joinDate=?,"+
					"creditAmount=?, picture=?, custStatus=? "+
					"WHERE custId = ?";
						
	static String QUERY_CUST_ID = "SELECT custId, custPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, creditAmount,"+ 
					"picture, custStatus FROM customerTable "+ 
					"WHERE custId LIKE ?";
	static String QUERY_CUST_NAME = "SELECT custId, custPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, creditAmount,"+ 
					"picture, custStatus FROM customerTable "+ 
					"WHERE surname LIKE ?";
	static String QUERY_CUST_PHONE = "SELECT custId, custPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, creditAmount,"+ 
					"picture, custStatus FROM customerTable "+ 
					"WHERE phone1 LIKE ?";
	public static String GET_CUST_BY_SURNAME = 
					"SELECT custId, name, surname, creditAmount, phone1" +
					" FROM customerTable WHERE surname LIKE ?";
	public static String GET_CUST_BY_PHONE = 
					"SELECT custId, name, surname, creditAmount, phone1" +
					" FROM customerTable WHERE phone1 LIKE ? "+
					"OR phone2 LIKE ?";

	public ClientManager(Connection conn)
	{
		this.conn = conn;
	}
	//This method is used when a Client is first generated by the GUI.
	public void commitClient(Client client)
	{
		try
		{
			this.client = client;
			String [] str = client.getClientAttrString();
			Statement stmt = conn.createStatement();
			String update;
			//for(int i = 0; i < str.length; i++)
			//{
			//	System.out.println(str[i]);
			//}
			
			update = "INSERT INTO customerTable VALUES('"+
				str[0]+"','"+str[1]+"','"+str[2]+"','"+str[3]+"','"+
				str[4]+"','"+str[5]+"','"+str[6]+"','"+str[7]+"','"+
				str[8]+"','"+str[9]+"','"+str[10]+"','"+str[11]+"','"+
				str[12]+"','"+str[13]+"','"+str[14]+"','"+
				str[15]+"',"+0.00+",'"+str[16]+"','"+str[17]+"')";
			int rows = stmt.executeUpdate(update);
			System.out.println("Rows changed: "+rows);
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}
	//This method searches the db for multiple records matching a
	//given input and returns a JTable to LookClientPanel, which graphically
	//displays the table to a user.
	public JTable searchClient(String inputString, String critString)
	{
		String input = inputString;
		String criteria = critString;
		try
		{
			if(criteria.equals("Last Name"))
			{
				input = input+"%";
				ResultSet rs;
				PreparedStatement ps = conn.prepareStatement(
					GET_CUST_BY_SURNAME,
					ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
				ps.setString(1, input);
				rs = ps.executeQuery();
				table = new Table();
				jTable = table.createStockSearchTable(rs);
			}
			else
			{
				ResultSet rs;
				PreparedStatement ps = conn.prepareStatement(
					GET_CUST_BY_PHONE,
					ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
				ps.setString(1, input +"%");
				ps.setString(2, input +"%");
				rs = ps.executeQuery();
				table = new Table();
				jTable = table.createStockSearchTable(rs);
			}
			
		}
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
		return jTable;
	}
	//This method is another search method used when there
	//can be only one record returned, as in the case
	//of searching by ID.
	public Client findClient(String inputString, String typeString)
	{
		String input = inputString;
		String type = typeString;
		try
		{
			PreparedStatement pstmt;
			ResultSet rs;
			if(type.equals("Client ID"))
			{
				pstmt = conn.prepareStatement(
						QUERY_CUST_ID);
				System.out.println("Client ID");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Last Name"))
			{
				pstmt = conn.prepareStatement(
						QUERY_CUST_NAME);
				System.out.println("Last Name");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Phone No"))
			{
				pstmt = conn.prepareStatement(
						QUERY_CUST_PHONE);
				System.out.println("Phone No");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else
			{
				pstmt = conn.prepareStatement(
						QUERY_CUST_ID);
				System.out.println("Client ID");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			
			if(rs.next())
			{
				rs.first();
				String [] queryArray = new String [18];
				queryArray[0] = rs.getString("custId");
				queryArray[1] = rs.getString("custPassword");
				queryArray[2] = rs.getString("idNumber");
				queryArray[3] = rs.getString("surname");
				queryArray[4] = rs.getString("name");
				queryArray[5] = rs.getString("initials");
				queryArray[6] = rs.getString("title");
				queryArray[7] = rs.getString("address1");
				queryArray[8] = rs.getString("address2");
				queryArray[9] = rs.getString("address3");
				queryArray[10] = rs.getString("address4");
				queryArray[11] = rs.getString("phone1");
				queryArray[12] = rs.getString("phone2");
				queryArray[13] = rs.getString("email");
				queryArray[14] = rs.getString("birthDate");
				queryArray[15] = rs.getString("joinDate");
				queryArray[16] = rs.getString("picture");
				queryArray[17] = rs.getString("custStatus");
				double credit = rs.getDouble("creditAmount");
				
				Client c = new Client(queryArray, credit);
				return c;
			}
			else
			{
				return null;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
			return null;
		}
	}
	//This method updates a client record on the DB, which allows
	//an end user to edit the details of a client.
	public void update(Client client)
	{
		try
		{
			this.client = client;
			String [] str = client.getClientAttrString();
			PreparedStatement ps = conn.prepareStatement(
					UPDATE_CUST);
		
			ps.setString(1, str[0]);
			ps.setString(2, str[1]);
			ps.setString(3, str[2]);
			ps.setString(4, str[3]);
			ps.setString(5, str[4]);
			ps.setString(6, str[5]);
			ps.setString(7, str[6]);
			ps.setString(8, str[7]);
			ps.setString(9, str[8]);
			ps.setString(10, str[9]);
			ps.setString(11, str[10]);
			ps.setString(12, str[11]);
			ps.setString(13, str[12]);
			ps.setString(14, str[13]);
			ps.setString(15, str[14]);
			ps.setString(16, str[15]);
			ps.setDouble(17, client.getCreditAmount());
			ps.setString(18, str[16]);
			ps.setString(19, str[17]);
			ps.setString(20, str[0]);
			System.out.println(str[0]);

			int rowsChanged = ps.executeUpdate();
			System.out.println(rowsChanged);
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}
	//This generates a client ID with the first three letters
	//of a client's surname followed by a number beginning at 1001.
	//Thus if there were two clients with the last name of Smith,
	//the first would have an ID of smi1001 and the second of smi1002.
	//This is achieved by extracting all existing IDs from the DB
	//and comparing the generated ID against them to ensure that
	//no duplicates are possible.
	public String generateClientId(String surname)
	{
		try
		{
			String lastName = surname;
			lastName = lastName.toLowerCase();
			Statement stmt = conn.createStatement(
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			ResultSet rs;
			String query = "SELECT custId FROM customerTable";
			rs = stmt.executeQuery(query);

			//Outer loop, which increments the suffix from 
			//1001 to a max of 11000.
			outer: for(int i = 0; i < 10000; i++)
			{
				String surSub = lastName.substring(0,3);
				int suffixInt = 1001+i;
				String suffix = "" + suffixInt;
				System.out.println(surSub);

				String subSurSub = surSub + suffix;
				System.out.println(subSurSub);

				inner://Inner loop, which checks for duplicates
				while(rs.next())
				{
					if(subSurSub.equals(rs.getString("custId")))
					{
						System.out.println("it's equal");
						continue outer;
					}
				}
				System.out.println("It's not");
				rs.close();
				return subSurSub;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
		return "Error";
	}
	
	//This method generates a unique password for each client,
	//which they'll use (independently of their ID) to log on for any
	//functions. In order to generate the password, each of five
	//positions in a string are given a random number from 1-10.
	//In practice, this means that approximately 100 000 unique passwords
	//are possible, while an inner loop ensures that there are no duplicates.
	public String generateClientPassword()
	{
		try
		{
			Statement stmt = conn.createStatement(
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			ResultSet rs;
			String query = "SELECT custPassword FROM customerTable";
			rs = stmt.executeQuery(query);
			outer: while(true)
			{
				int [] nums = new int []{1,2,3,4,5,6,7,8,9,0};
				int pos1, pos2, pos3, pos4, pos5;
				pos1 = ((int)(Math.random()*10.0));
				pos2 = ((int)(Math.random()*10.0));
				pos3 = ((int)(Math.random()*10.0));
				pos4 = ((int)(Math.random()*10.0));
				pos5 = ((int)(Math.random()*10.0));

				stringPass = "" + pos1 + pos2 + pos3 + pos4 + pos5;
				System.out.println(stringPass);
				PasswordHasher passwordHasher = new PasswordHasher("SHA-256");
				byte[] passwordBytes = stringPass.getBytes();
				String passwordHash = passwordHasher.hashPassword(
									passwordBytes);
				inner:
				while(rs.next())
				{
					if(passwordHash.equals(rs.getString("custPassword")))
					{
						System.out.println("it's equal");
						continue outer;
					}
				}
				System.out.println("It's not");
				rs.close();
				return passwordHash;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();	
		}
		catch(NoSuchAlgorithmException nsae)
		{
			nsae.printStackTrace();
		}
		return "";
	}
	public String getClientPlainTextPassword()
	{
		return stringPass;
	}
	public String [] getInfoPanel(String clientId)
	{
		String [] output = new String[7];
		try
		{
			String fineCount = "SELECT count(custId) from eventtable "+
			"where custId like '"+clientId+"' and eventType like 'Fine'";
			String loanCount = "SELECT count(custId) from eventtable "+
			"where custId like '"+clientId+"' and eventType like 'Loan'";
			String creditCheck = "SELECT creditAmount from customerTable "+
			"WHERE custId like '"+clientId+"'";
			String joinDate = "SELECT joinDate from customerTable "+
			"WHERE custId like '"+clientId+"'";
			String age = "SELECT (YEAR(CURDATE())-YEAR(birthDate)) "+
			"- (RIGHT(CURDATE(),5)<RIGHT(birthDate,5)) AS age FROM"+
			" customerTable WHERE custId = '"+clientId+"'";
			String lastDate = "SELECT eventDate FROM eventTable "+
			"WHERE custId like '"+clientId+"' AND eventType like "+
			"'Loan' ORDER BY eventDate DESC LIMIT 1";
			String lastRent1 = "SELECT eventId FROM eventTable "+
			"WHERE custId like '"+clientId+"' AND eventType like "+
			"'Loan' ORDER BY eventDate DESC LIMIT 1";
			String eventId;
			
			ResultSet rs;
			Statement stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(age);
			rs.next();
			output[0] = rs.getString(1);
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(creditCheck);
			rs.next();
			output[1] = ""+ rs.getDouble(1);
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(joinDate);
			rs.next();
			output[2] = rs.getDate(1).toString();
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(loanCount);
			rs.next();
			output[3] = ""+ rs.getInt(1);
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(lastRent1);
			rs.next();
			eventId = rs.getString(1);
			String lastRent2 = "SELECT stockTable.title FROM stockTable,"+
			"custEventTable WHERE stockTable.stockId LIKE "+
			"(SELECT stockId FROM custEventTable WHERE eventId LIKE '"+
				eventId+"'"+
			 "AND eventLineNo LIKE '1')";
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(lastRent2);
			rs.next();
			output[4] = rs.getString(1);
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(lastDate);
			rs.next();
			output[5] = rs.getDate(1).toString();
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(fineCount);
			rs.next();
			output[6] = ""+rs.getInt(1);
			
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
		return output;
		//SideInfoPanel sp = new SideInfoPanel(conn, output);
	}
	
}